﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SNPAD.Model;
using System.Data.SqlClient; 

namespace SNPAD.DAL
{
    public class OperatorsDAL
    {
        SqlCommand cmd = new SqlCommand();
        public OperatorsDAL()
        {
            cmd.Connection = SQLHelper2.Conn;
        }
        ~OperatorsDAL()
        {
            cmd = null; 
        }
        /// <summary>
        /// 将数据加入到数据库中，如果返回true说明添加成功
        /// </summary>
        /// <param name="u">要加入到数据库表中的Users对象</param>
        /// <returns></returns>
        public bool Insert(operators  u)
        {
            try
            {
                bool ok = false;
                cmd.CommandText = "insert into operators(curr_user_id,curr_user_name,password) values(@a,@b,@c)";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@a", u.curr_user_id);
                cmd.Parameters.AddWithValue("@b", u.curr_user_name);
                cmd.Parameters.AddWithValue("@c", u.password);
                int count = 0;
                count = cmd.ExecuteNonQuery();

                if (count > 0)
                    ok = true;
                return ok;
            }
            catch (SqlException ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 将数据从数据库中删除，如果返回true说明删除成功
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public bool Delete(string userid)
        {
            bool ok = false;

            cmd.CommandText = "delete from operators where curr_user_id=@a";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@a", userid );
            int count = 0;
            
            count = cmd.ExecuteNonQuery();
             
            if (count > 0)
                ok = true;
            return ok;
        }

        /// <summary>
        /// 对数据库中的数据进行修改，如果返回true说明修改成功
        /// </summary>
        /// <param name="username"></param>
        /// <param name="u"></param>
        /// <returns></returns>
        public bool Update(string userid, operators  u)
        {
            bool ok = false;
            cmd.CommandText = "update operators set curr_user_id=@a,password=@b,curr_user_name=@c where userid=@un";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@a", u.curr_user_id );
            cmd.Parameters.AddWithValue("@b", u.password );
            cmd.Parameters.AddWithValue("@c", u.curr_user_name); 
            cmd.Parameters.AddWithValue("@un", userid);
            int i = 0;
             
            i = cmd.ExecuteNonQuery();
        
            if (i > 0)
                ok = true;
            return ok;
        }

        /// <summary>
        /// 查询数据库中所有的数据，返回List<>集合
        /// </summary>
        /// <returns></returns>
        public List<operators > Select()
        {
            List<operators> list = new List<operators>();
            cmd.CommandText = "select *from operators";
            
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    operators u = new operators(); 
                    u.curr_user_id = dr["curr_user_id"].ToString();
                    u.password = dr["password"].ToString();
                    u.curr_user_name = dr["curr_user_name"].ToString();
                    u.Role_code  = dr["Role_code"].ToString();
                    list.Add(u);
                }
            }
             
            return list;
        }

        /// <summary>
        /// 查询数据库中某条数据，返回List<>集合
        /// </summary>
        /// <param name="username">要查询的数据的用户名</param>
        /// <returns></returns>
        public List<operators> Select(string userid)
        {
            List<operators> list = new List<operators>();
            cmd.CommandText = "select *from operators where curr_user_id=@a";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@a", userid);
             
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                dr.Read();
                operators u = new operators();
              
                u.curr_user_id = dr["curr_user_id"].ToString();
                u.password  = dr["password"].ToString();
                u.curr_user_name = dr["curr_user_name"].ToString();
                //u.Sex = (Boolean)dr["sex"]; 
                u.Role_code = dr["Role_code"].ToString();
                list.Add(u);
            }
           
            return list;
        }
    }
}
